package com.xhh.nphm.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xhh.nphm.common.core.domain.entity.SysUser;
import com.xhh.nphm.system.domain.TtelGetDealEty;
import com.xhh.nphm.system.domain.TworkFormBaseEty;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.checkerframework.checker.index.qual.SameLen;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

@Repository
public interface TelGetDealDao extends BaseMapper<TtelGetDealEty> {

    @Select("SELECT pkId,wsCode,liftTelNo,liftWorker,fmAreaName,fromTelA,fromName,openX,wkPkId,fromTime,stateFlag," +
            "stateDesc,wfSltKey,callId,liftWorkNo,liftEnd,liftLength,tapeId,liftInt,liftStr,fmAreaNo,fromSex," +
            "fmAddress,fromTelB,fromTelC,fmEmail,fmCerName,fmCerNo,openFrom,fmContent,fmSpareA,fmSpareB,dfmClaNo," +
            "dfmClaName,dcntAcode,dcntAname,dcntBcode,dcntBname,wsKey,repeatFlag,repeatNos,xinputTime,xdealDesc," +
            "xsysDesc,xspare,yworkNo,yworker,yeditTime,ydealDesc,ysysDesc,yspare,wkFmFlag,wkFmNo,wkSpare,autoGetFlag," +
            "isDel,spareInt,spareString,flagInt,flagString,fkint,fkstring,hideTel,keyWord,mark,accSubject," +
            "reenterState,affectScope,lable " +
            "FROM tTelGetDeal " +
            "WHERE pkId=#{id} ")
    TtelGetDealEty selectById(@Param("id") Serializable id);

    /**
     * 查询来电记录草稿列表
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,FromTime,HideTel,StateFlag,XSysDesc as myDesc from tTelGetDeal " +
            "where StateFlag=101 and IsDel=0 " +
            "[[and liftWorkNo=#{liftWorkNo}]] [[and wsCode=#{wsCode}]] [[and fromTelA =#{fromTelA}]] " +
            "[[and fromName =#{fromName}]] [[and fmAreaName =#{fmAreaName}]] " +
            "order by PkId desc")
    List<TtelGetDealEty> listDraft(TtelGetDealEty ttelGetDealEty);

    /**
     * 查询待处理来电记录列表
     *
     * @param userNo 当前登录账号
     * @param ttelGetDealEty
     * @return
     */
    @Select("SELECT t.PkId, t.WsCode, t.LiftTelNo, t.LiftWorker, t.FmAreaName,t.DFmClaName,t.DCntAName, t.FromTelA, t.FromName, t.OpenX, t.WkPkId, t.FromTime, t.HideTel," +
            " convert(varchar(10), XInputTime, 20) AS smDate, t.StateFlag, t.XSysDesc FROM tTelGetDeal t " +
            "inner join tm_tTelGetDeal_temp tm on t.PkId=tm.telId left join tm_sys_user tsu on tsu.user_no = t.LiftWorkNo " +
            "where t.StateFlag IN (120, 122)  AND t.IsDel = 0 and tm.userNo=#{userNo} and tsu.carrier_type = #{carrierType} [[and t.wsCode=#{tel.wsCode}]] [[and t.fromTelA =#{tel.fromTelA}]] [[and t.fromName =#{tel.fromName}]] [[and t.fmAreaName =#{tel.fmAreaName}]] [[and t.DCntAName =#{tel.dcntAname}]] order by NEWID()")
    List<TtelGetDealEty> listWait(@Param("userNo") String userNo,@Param("carrierType") Integer carrierType,@Param("tel") TtelGetDealEty ttelGetDealEty);

    /**
     * 查询我接听的来电列表
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,convert(varchar(19),FromTime,20) as FromTime,HideTel,StateFlag,XSysDesc as myDesc,PkId as myOP from tTelGetDeal where StateFlag not in (0,101) and IsDel=0 [[and wsCode =#{wsCode}]] [[and fromTelA like #{fromTelA}]] [[and fromName like #{fromName}]] [[and fmAreaName like #{fmAreaName}]] [[and liftWorkNo=#{liftWorkNo}]] order by PkId desc")
    List<TtelGetDealEty> myGetList(TtelGetDealEty ttelGetDealEty);


    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,convert(varchar(19),FromTime,20) as FromTime,HideTel,convert(varchar(10),YEditTime,20) as bkDate,YSysDesc as myDesc,PkId as myOP from tTelGetDeal where StateFlag=121 and IsDel=0 [[and wsCode=#{wsCode}]] [[and fromTelA like #{fromTelA}]] [[and fromName like #{fromName}]]  [[and fmAreaName like #{fmAreaName}]] order by PkId desc")
    List<TtelGetDealEty> listPending(TtelGetDealEty ttelGetDealEty);

    /**
     * 我处理的来电列表
     *
     * @param ttelGetDealEty
     * @return
     */
    //todo: 老系统用的是yWorkNo
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,convert(varchar(19),FromTime,20) as FromTime,HideTel,convert(varchar(19),YEditTime,20) as opDate,StateFlag,YSysDesc as myDesc,PkId as myOP from tTelGetDeal " +
            "where StateFlag in (121,122,125,135,136) and IsDel=0 [[and wsCode=#{wsCode}]] [[and yWorkNo=#{yWorkNo}]] " +
            "[[and fromTelA like #{fromTelA}]] [[and fromName like #{fromName}]]  [[and fmAreaName like #{fmAreaName}]] " +
            "order by PkId desc")
    List<TtelGetDealEty> listHandle(TtelGetDealEty ttelGetDealEty);

    /**
     * 坐席办结来电列表
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,convert(varchar(19),FromTime,20) as FromTime,HideTel,StateFlag,XSysDesc as myDesc from tTelGetDeal " +
            "where StateFlag in (131,132,135,136) and IsDel=0 [[and wsCode=#{wsCode}]] " +
            "[[and fromTelA like #{fromTelA}]] [[and fromName like #{fromName}]]  [[and fmAreaName like #{fmAreaName}]] " +
            "order by PkId desc")
    List<TtelGetDealEty> listFinish(TtelGetDealEty ttelGetDealEty);

    /**
     * 已派工单来电列表
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,convert(varchar(19),FromTime,20) as FromTime,HideTel,convert(varchar(19),YEditTime,20) as opDate,WkFmNO,YSysDesc as myDesc from tTelGetDeal where StateFlag=125 and IsDel=0 [[and wsCode=#{wsCode}]] [[and fromTelA like #{fromTelA}]] [[and fromName like #{fromName}]]  [[and fmAreaName like #{fmAreaName}]] order by PkId desc")
    List<TtelGetDealEty> listWork(TtelGetDealEty ttelGetDealEty);

    @Select("SELECT COUNT(pkId) FROM tTelGetDeal WHERE YEAR(xinputTime)=#{fpYear} AND MONTH(xinputTime)=#{fpMth} AND DAY(xinputTime)=#{fpDay} AND LEN(wsCode)>0")
    String selectByDate(@Param("fpYear") String fpYear, @Param("fpMth") String fpMth, @Param("fpDay") String fpDay);


    @Update("<script>" +
            "UPDATE tTelGetDeal SET IsDel=1 WHERE PkId IN " +
            "<foreach collection='array' item='id' open='(' separator=',' close=')'>#{id}</foreach>"
            + "</script>")
    int deleteByIds(String[] ids);

    /**
     * 根据 fromTelA 查询 来电历史记录
     * @param fromTelA
     * @return
     */
    @Select("select PkId ,FromTelA,OpenX,WkPkId,FromName,WkFmNO,convert(varchar(19),FromTime,20) as FromTime," +
            "FmContent,DFmClaName,DcntAName,StateDesc,WsCode,WsKey,YSysDesc  from tTelGetDeal where IsDel = 0 and FromTelA=#{fromTelA}" +
            "[[AND WsCode = #{wsCode}]] [[AND WsKey = #{wsKey}]] [[AND WkFmNo = #{wkFmNo}]] [[AND CHARINDEX(#{fmContent},FmContent) > 0]] [[AND FmAddress = #{fmAddress} ]] [[AND AccSubject = #{accSubject} ]] [[AND CHARINDEX(#{keyWord},KeyWord) > 0 ]] order by PkId desc")
    List<TtelGetDealEty> historyLiaison(TtelGetDealEty ttelGetDealEty);

    /**
     * 查询待值班长处理列表
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,FromTime,HideTel,XInputTime,XSysDesc from tTelGetDeal " +
            "where StateFlag=120 and IsDel=0 [[ and liftWorkNo=#{liftWorkNo}]] order by PkId desc")
    List<TtelGetDealEty> listMtrWait(TtelGetDealEty ttelGetDealEty);

    /**
     * 查询值班长退回列表
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,convert(varchar(19),FromTime,20) as FromTime,HideTel,convert(varchar(10),YEditTime,20) as bkDate,YSysDesc from tTelGetDeal " +
            "where StateFlag=121 and IsDel=0 [[ and liftWorkNo=#{liftWorkNo}]] order by PkId desc")
    List<TtelGetDealEty> listMtrBack(TtelGetDealEty ttelGetDealEty);

    /**
     * 查询我办结的来电列表
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorker,FmAreaName,FromTelA,FromName,FromTime,HideTel,XInputTime,StateFlag,XSysDesc from tTelGetDeal " +
            " where StateFlag in (131,132) and IsDel=0 [[ and liftWorkNo=#{liftWorkNo}]] order by PkId desc")
    List<TtelGetDealEty> listMyOver(TtelGetDealEty ttelGetDealEty);
    @Select("select top 2 left(DCntACode,4) as dalei,count(1) num from tTelGetDeal  where fromTelA=#{fromTelA} group by left(DCntACode,4) order by num desc")
    List<Map<String,String>> selectCountProblem(String loginName);

    @Select("select count(1) from  (select * from tTelGetDeal where fromTelA=#{fromTelA}) t1 inner join tWorkFormBase t2 on t1.pkid=t2.GetDealPK " +
            "inner join twfjudge t3 on t2.pkid=t3.wfid where t3.judgec=1")
    String noLike(String fromTelA);

    /**
     * 随机获取N条待处理来电记录
     * @param num
     * @return
     */
    @Select("SELECT top (#{num}) tm.telId,t.PkId, t.WsCode, t.LiftTelNo, t.LiftWorker, t.FmAreaName, t.FromTelA, t.FromName, t.OpenX, t.WkPkId, t.FromTime, t.HideTel, convert(varchar(10), XInputTime, 20) AS smDate, t.StateFlag, t.XSysDesc FROM tTelGetDeal t left join tm_tTelGetDeal_temp tm  on t.PkId=tm.telId where t.StateFlag IN (120, 122)  AND t.IsDel = 0 and  tm.telId is  NULL  order by NEWID()")
    List<TtelGetDealEty> getListWait(Integer num);

    /**
     * 查询未获取待处理来电记录条数
     * @param carrierType 运营商标识
     * @return 条数
     */
    @Select("SELECT count(0) FROM tTelGetDeal t left join tm_tTelGetDeal_temp tm  on t.PkId=tm.telId left join tm_sys_user tsu on tsu.user_no = t.LiftWorkNo " +
            "where t.StateFlag IN (120, 122)  AND t.IsDel = 0 and  tm.telId is NULL and tsu.carrier_type = #{0}")
    Integer countListWait(Integer carrierType);

    /**
     * 未获取待处理来电记录列表数据
     * @param ttelGetDealEty
     * @return
     */
    @Select("SELECT tm.telId,t.PkId, t.WsCode, t.LiftTelNo, t.LiftWorker, t.FmAreaName, t.FromTelA, t.FromName, t.OpenX, t.WkPkId, t.FromTime, t.HideTel," +
            " convert(varchar(10), XInputTime, 20) AS smDate, t.StateFlag, t.XSysDesc,t.mark " +
            "FROM tTelGetDeal t left join tm_tTelGetDeal_temp tm on t.PkId=tm.telId left join tm_sys_user tsu on tsu.user_no = t.LiftWorkNo " +
            "where t.StateFlag IN (120, 122)  AND t.IsDel = 0 and tm.telId is NULL and tsu.carrier_type = #{carrierType} " +
            "[[and t.wsCode=#{tel.wsCode}]] [[and t.fromTelA =#{tel.fromTelA}]] [[and t.fromName =#{tel.fromName}]] [[and t.fmAreaName =#{tel.fmAreaName}]] [[and t.DCntAName =#{tel.dcntAname}]]  order by CHARINDEX('110转接',FromName) desc,mark desc, NEWID()")
    List<TtelGetDealEty> noGetListWait(@Param("carrierType") Integer carrierType,@Param("tel") TtelGetDealEty ttelGetDealEty);

    /**
     * 重录件列表数据
     * @param ttelGetDealEty
     * @return
     */
    @Select("select t.PkId,t.WsCode,t.LiftTelNo,t.LiftWorker,t.FmAreaName,t.FromTelA,t.FromName,t.YWorkNo,t.YWorker," +
            "convert(varchar(19),FromTime,20) as FromTime,t.HideTel,convert(varchar(10),YEditTime,20) as bkDate,t.YSysDesc" +
            " from tTelGetDeal t left join tm_sys_user tsu on tsu.user_no = t.LiftWorkNo where t.IsDel=0 and t.reenterState=1 and tsu.carrier_type = #{carrierType} " +
            "[[and t.wsCode=#{tel.wsCode}]] [[and t.fromTelA =#{tel.fromTelA}]] [[and t.fromName =#{tel.fromName}]] [[and t.fmAreaName =#{tel.fmAreaName}]] [[and t.DCntAName =#{tel.dcntAname}]]  order by PkId desc")
    List<TtelGetDealEty> reenterList(@Param("carrierType") Integer carrierType,@Param("tel") TtelGetDealEty ttelGetDealEty);

    /**
     * 紧急件列表数据
     * @param ttelGetDealEty
     * @return
     */
    @Select("select t.PkId,t.WsCode,t.LiftTelNo,t.LiftWorker,t.FmAreaName,t.FromTelA,t.FromName,t.YWorkNo,t.YWorker," +
            "convert(varchar(19),FromTime,20) as FromTime,t.HideTel,convert(varchar(10),YEditTime,20) as bkDate,t.YSysDesc" +
            " from tTelGetDeal t left join tm_sys_user tsu on tsu.user_no = t.LiftWorkNo where t.IsDel=0 and t.mark=1 and tsu.carrier_type = #{carrierType}" +
            "[[and t.wsCode=#{tel.wsCode}]] [[and t.fromTelA =#{tel.fromTelA}]] [[and t.fromName =#{tel.fromName}]] [[and t.fmAreaName =#{tel.fmAreaName}]] [[and t.DCntAName =#{tel.dcntAname}]]  order by PkId desc")
    List<TtelGetDealEty> urgentList(@Param("carrierType") Integer carrierType,@Param("tel") TtelGetDealEty ttelGetDealEty);

    /**
     * 查询通话记录列表数据
     *
     * @param ttelGetDealEty 查询条件
     * @return 列表数据
     */
    @Select("select PkId,WsCode,LiftTelNo,LiftWorkNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,HideTel," +
            "convert(varchar(19),FromTime,20) as FromTime,WfSltKey,StateFlag from tTelGetDeal" +
            " where StateFlag != 0 and IsDel=0 [[and WsCode=#{wsCode}]] " +
            "[[and FromTelA=#{fromTelA}]] [[and FromName = #{fromName}]] [[and LiftWorkNo = #{liftWorkNo}]] " +
            "[[and FmContent =#{fmContent}]] [[and FromTime &gt;= #{startTime}]] [[and FromTime &lt;= #{endTime}]] ")
    List<TtelGetDealEty> telGetAllList(TtelGetDealEty ttelGetDealEty);

    /**
     * 释放获取件列表数据
     * @param ttelGetDealEty
     * @return
     */
    @Select("SELECT t.PkId, t.WsCode, t.LiftTelNo, t.LiftWorker, t.FmAreaName,t.DFmClaName,t.DCntAName, t.FromTelA, t.FromName, t.OpenX, t.WkPkId, t.FromTime, t.HideTel, convert(varchar(10), XInputTime, 20) AS smDate, t.StateFlag, t.XSysDesc,tm.userName FROM tTelGetDeal t inner join tm_tTelGetDeal_temp tm  on t.PkId=tm.telId where t.StateFlag IN (120, 122)  AND t.IsDel = 0  [[and t.wsCode=#{tel.wsCode}]] [[and t.fromTelA =#{tel.fromTelA}]] [[and t.fromName =#{tel.fromName}]] [[and t.fmAreaName =#{tel.fmAreaName}]] [[and t.DCntAName =#{tel.dcntAname}]] order by NEWID()")
    List<TtelGetDealEty> freedListWaitJson(@Param("tel") TtelGetDealEty ttelGetDealEty);
    /**
     * 查询通话记录列表数据
     *
     * @param ttelGetDealEty 查询条件
     * @return 列表数据
     */
    @Select("select PkId as myPkVal,WsCode,LiftTelNo,LiftWorkNo,LiftWorker,FmAreaName,FromTelA,FromName,OpenX,WkPkId,HideTel," +
            "convert(varchar(19),FromTime,20) as FromTime,WfSltKey,StateFlag,PkId as opScan from tTelGetDeal" +
            " where StateFlag != 0 and IsDel=0")
    List<TtelGetDealEty> telGetList(TtelGetDealEty ttelGetDealEty);

    /**
     * 创建工单时，更新电话记录
     * @param telRecord
     * @return
     */
    @Update("update tTelGetDeal set yworkNo=#{yWorkNo},,yworker=#{yworker},yeditTime=getdate(),ydealDesc=#{ydealDesc},,ysysDesc=#{ysysDesc},wkFmFlag=1,wkPkId=#{wkPkId},wkFmNO=#{wkFmNo},,wfSltKey=#{wfSltKey},stateFlag=#{stateFlag},stateDesc=#{stateDesc} where pkId=#{pkId}")
    int whenWFCreateSet(TtelGetDealEty telRecord);

    /**
     * 通过信件id查询信件
     * @param PkId
     * @return
     */
    @Select("select * from tTelGetDeal where PkId=#{pkId}")
    TtelGetDealEty selectByTelPkId(int pkId);

    /**
     * 通过keyword查询同类电话
     * @param tagName keyword
     * @return
     */
    @Select("select t2.pkId,wsCode,liftTelNo,liftWorker,fmAreaName,fromTelA,fromName,openX,wkPkId,fromTime,stateFlag, " +
            "stateDesc,wfSltKey,callId,liftWorkNo,liftEnd,liftLength,tapeId,liftInt,liftStr,fmAreaNo,fromSex, " +
            "fmAddress,fromTelB,fromTelC,fmEmail,fmCerName,fmCerNo,openFrom,fmContent,fmSpareA,fmSpareB,dfmClaNo, " +
            "dfmClaName,dcntAcode,dcntAname,dcntBcode,dcntBname,wsKey,repeatFlag,repeatNos,xinputTime,xdealDesc, " +
            "xsysDesc,xspare,yworkNo,yworker,yeditTime,ydealDesc,ysysDesc,yspare,wkFmFlag,wkFmNo,wkSpare,autoGetFlag," +
            "isDel,spareInt,spareString,flagInt,flagString,fkint,fkstring,hideTel,keyWord,mark,accSubject, " +
            "reenterState,affectScope,lable " +
            "from tm_makeTag_Tel t1 " +
            "left join tTelGetDeal t2 on t1.tel_id = t2.pkId " +
            "where CHARINDEX(#{keyWord}, t1.tag_name)>0 and t1.is_del = 0 and t2.isDel = 0 " +
            "[[AND t2.WsCode = #{wsCode}]] [[AND t2.WsKey = #{wsKey}]] [[AND t2.WkFmNo = #{wkFmNo}]] [[AND CHARINDEX(#{fmContent},t2.FmContent) > 0]] [[AND t2.FmAddress = #{fmAddress} ]] [[AND AccSubject = #{accSubject} ]] order by PkId desc ")
    List<TtelGetDealEty> relevanceRec(TtelGetDealEty telGetDeal);

    @Select("select PkId ,FromTelA,OpenX,WkPkId,FromName,WkFmNO,convert(varchar(19),FromTime,20) as FromTime," +
            "FmContent,DFmClaName,DcntAName,StateDesc,WsCode,WsKey,YSysDesc  from tTelGetDeal where IsDel = 0 and CHARINDEX(#{accSubject},AccSubject) > 0 " +
            "[[AND WsCode = #{wsCode}]] [[AND WsKey = #{wsKey}]] [[AND WkFmNo = #{wkFmNo}]] [[AND CHARINDEX(#{fmContent},FmContent) > 0]] [[AND FmAddress = #{fmAddress} ]] order by PkId desc")
    List<TtelGetDealEty> accSubjectDraw(TtelGetDealEty ttelGetDealEty);

    /**
     *
     * @param ttelGetDealEty
     * @return
     */
    @Select("select PkId ,FromTelA,OpenX,WkPkId,FromName,WkFmNO,convert(varchar(19),FromTime,20) as FromTime," +
            "FmContent,DFmClaName,DcntAName,StateDesc,WsCode,WsKey,YSysDesc  from tTelGetDeal where IsDel = 0 and FmAddress=#{fmAddress}" +
            "[[AND WsCode = #{wsCode}]] [[AND WsKey = #{wsKey}]] [[AND WkFmNo = #{wkFmNo}]] [[AND CHARINDEX(#{fmContent},FmContent) > 0]] [[AND FromTelA=#{fromTelA}]] [[AND AccSubject = #{accSubject} ]] [[AND CHARINDEX(#{keyWord},KeyWord) > 0 ]] order by PkId desc")
    List<TtelGetDealEty> telHistoryByAddress(TtelGetDealEty ttelGetDealEty);
}
